drop table librarymember;
create table librarymember(
		member_id varchar2(15) PRIMARY KEY,
		member_password varchar2(15) NOT NULL,
		member_name varchar2(10) NOT NULL,
		register_no1 varchar2(6) NOT NULL,
		register_no2 varchar2(7) NOT NULL,
		phone varchar2(15) NOT NULL,
		address varchar2(50) NOT NULL
);

--***************************************** foreign key 설정************************************
-- on delete cascade : fk의 원본 레코드가 삭제되면 fk가 함께 삭제된다.
-- on delete set null : fk의 원본 레코드가 삭제되면 fk를 null 로 한다.
-- on update cascade : fk의 원본 레코드가 업데이트되면 fk도 함께 업데이트한다. 
--   => (주의!) on update cascade는 MySQL에서만 지원하므로, Oracle DB에선 Trigger 이용하도록
--**********************************************************************************************

-- 게시판 
drop table board;
delete from board;
select * from board;
create table board(
	board_no number primary key,
	board_title varchar2(100) not null, 
	board_date varchar2(30) not null,
	content clob not null,
	image varchar2(100),
	member_id varchar2(15) not null,
	book_application_mark varchar2(50),
	constraint m_id_fk foreign key(member_id) references librarymember(member_id) on delete cascade,
	constraint b_id_fk foreign key(book_application_mark) references book(book_application_mark) on delete cascade
);
insert into board(board_no, board_title, board_date, content, image, member_id, book_application_mark)
                values(2, '첫 글', sysdate(), '안녕하세요.', null, 'id-1', null);

-- 게시판 Date
select sysdate from dual;
select to_char(sysdate, 'yyyy.mm.dd hh24:mi:ss') from dual;

-- 게시판 Table number
drop sequence board_no;
create sequence board_no
	nocycle
;
select board_no.nextval from dual;

drop table book;
create table book (
		book_application_mark varchar2(50)  PRIMARY KEY,
		book_name varchar2(50)  NOT NULL,
		author varchar2(50)  NOT NULL,
		translator varchar2(50),
		publisher varchar2(50)  NOT NULL,
		publish_year number(4) NOT NULL,
		book_rental varchar2(12)   NOT NULL,
		member_id varchar2(15),
		constraint b_rental_ck CHECK(book_rental in('대출가능', '대출중')) 
);


select * from book;
select * from librarymember;

















